# -*- coding: utf-8 -*-
__author__ = 'liubei'

import db_engine
import api_statistics
import aes
from datetime import datetime
import db_dashboard
import az_dateutil
import pytz
import time
import rest
import surveyInstanceDB
import topic
from openpyxl import load_workbook

msgCatalogSheetIndex = 6
msgSheetIndex = 5
adheranceDateIndex = 4
userOperationReportIndex = 3

pageLength = 250

itemList = ['', 'Patient E-code', 'Site', 'Age', 'Gender', 'Number of completed clinical survey',
            'Number of total pushed survey', 'Acceptance on Clinical Survey', 'Times of User did the heath quiz',
            'Number of completed Health Knowledge Quiz', 'Number of total pushed Quiz',
            'App Loyalty-1', 'App Loyalty-2', 'Last Date of Opening App', 'App Type',
            'Number of days since App installation/registration', 'Medicine Adherance',
            'Adherance days since registration',
            'Medicine Adherance (Adherance day since registration/number of days since registration)',
            'Exercises Trends (Activity Minutes this week/Activity Minutes last week)',
            'Total Activity minutes in last calendar week(min)',
            'Last Target Activity minutes in last calendar week(min)', 'Has user set carer',
            'Smoking status', 'Hypertension Status', 'Medication Reminder', 'Health Index',
            'Ranking of Health Index (Percentage in total user)',
            'Has the user reach the Exercise Target Activity minutes/personal goals(min)',
            'Number of times the user has saved exercise', 'Number of times the user has saved weight',
            'Number of times the user has saved BP', 'Number of times the user has saved LDL-C',
            'Number of times the user has saved HDL-C', 'Number of times the user has saved TC',
            'Number of times the user has saved TG', 'Systolic BP value (Last reported value)',
            'Date of last Systolic BP input', 'Diastolic BP value (Last reported value)',
            'Date of last Diastolic BP input', 'LDL Value (Last reported value)', 'Date of last LDL data input',
            'HDL Value (Last reported value)', 'Date of Last HDL data input', 'TC Value (Last reported value)',
            'Date of Last TC data input',
            'TG Value (Last reported value)', 'Date of Last TG data input', 'BMI value (Last reported value)',
            'Date of Last BMI value input', 'BMI target (Last reported value and date)', 'Date of Last BMI target input']

def open_excelByOpenXL(file='file.xlsx'):
    db_engine.connect("az", host=db_engine.host)
    wb = load_workbook(file)
    cpTable = wb._sheets[msgSheetIndex]
    cpCatalogTable = wb._sheets[msgCatalogSheetIndex]
    for i in range(2, 65, 1):
        msgNo = int(cpTable.cell(row=i, column=1).value)
        print(msgNo)
        lMsgs = api_statistics.LongMessagePopularity.objects(msgNo=str(msgNo))
        userCount = 0
        if len(lMsgs) == 0:
            lMsg = api_statistics.LongMessagePopularity(totalCount=0)
        else:
            lMsg = lMsgs[0]
            userCount = len(lMsg.users)
        cpTable.cell(row=i, column=2, value=lMsg.totalCount)
        cpTable.cell(row=i, column=3, value=userCount)
        if userCount == 0:
            cpTable.cell(row=i, column=4, value=userCount)
        else:
            cpTable.cell(row=i, column=4, value="=C%s/B%s" % (i, i))

    for i in range(2, 10, 1):
        index = i + 8
        print 'index %s' % index
        lCats = api_statistics.LongMessageCatalogPopularity.objects(catalogNo=str(index))
        userCount = 0
        if len(lCats) == 0:
            lCat = api_statistics.LongMessageCatalogPopularity(totalCount=0)
        else:
            lCat = lCats[0]
            userCount = len(lCat.users)
        cpCatalogTable.cell(row=i, column=3, value=lCat.totalCount)
        cpCatalogTable.cell(row=i, column=4, value=userCount)
        if userCount == 0:
            cpCatalogTable.cell(row=i, column=5, value=0)
        else:
            cpCatalogTable.cell(row=i, column=5, value="=C%s/D%s" % (i, i))

    # cpDetailedAdheranceDateTable = wb._sheets[detailedAdheranceDateIndex]
    cpAdheranceDateTable =  wb._sheets[adheranceDateIndex]
    cpUserOperationReportTable =  wb._sheets[userOperationReportIndex]
    users = db_engine.User.objects(userType__ne=2).order_by('createDate')
    print "===== total count : %s =====" % users.count()
    index = 2
    medicineIndex = 2
    aesCipher = aes.AESCipher(aes.aes_key)
    healthDic = {}
    for i in range(0, users.count()):
        user = users[i]
        print user.patientCode
        if True:
            print 'index %s' % index

            # patientCode
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Patient E-code'), value=user.patientCode)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Site'), value=user.patientCode[3:5])
            cpUserOperationReportTable.cell(row=index, column=itemList.index('App Type'), value='e-Questionnaire' if user.nickName=='' or user.userType==3 else 'Family Doctor')
            # year old
            birthday = aesCipher.decrypt(user.userBirthday)
            if birthday:
                print birthday
                try:
                    if "-" in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y-%m-%d')
                    elif "." in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y.%m.%d')
                    else:
                        birthdayDate = datetime.strptime(birthday, '%Y%m%d')
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Age'), value=calculate_age(birthdayDate))
                except Exception as err:
                    print str(err)

            # sexy
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Gender'), value="Male" if aesCipher.decrypt(user.userGender) == "1" else "Female")

            #======= Adherance day since registration begin =======
            medicines = db_engine.Medicine.objects(userPhone=user.userPhone).order_by('-createDate')
            count = 0
            medicineList = []
            for medicine in medicines:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Adherance days since registration'), value=count)

            if len(medicineList) == 0:
                cpAdheranceDateTable.cell(row=medicineIndex, column=1, value=user.patientCode)
                cpAdheranceDateTable.cell(row=medicineIndex, column=2, value='')
                medicineIndex += 1
            else:
                for x in range(0, len(medicineList), 1):
                    cpAdheranceDateTable.cell(row=medicineIndex, column=1, value=user.patientCode)
                    cpAdheranceDateTable.cell(row=medicineIndex, column=2, value=medicineList[x])
                    medicineIndex += 1
            #======= Adherance day since registration end =======

            # number of days since registration
            createDate = az_dateutil.timestamp2datetime(user.createDate)
            diffCreateDate = diffDay(createDate)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of days since App installation/registration'), value=diffCreateDate)
            #Number of total pushed Quiz
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed Quiz'), value=int(diffCreateDate/7))

            # Medicine Adherance(Adherance day in last 30 days)
            now = int(time.time())
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisDate = datetime.fromtimestamp(now, tz)
            now = int(az_dateutil.date2timestamp(
                thisDate.replace(thisDate.year, thisDate.month, thisDate.day, 0, 0, 0))) + 16 * 60 * 60

            medicines30 = db_dashboard.queryMedicinelistBetweenDays(now, now - 30 * 24 * 60 * 60, user.userPhone)
            count = 0
            medicineList = []
            for medicine in medicines30:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Medicine Adherance'), value=count)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Medicine Adherance (Adherance day since registration/number of days since registration)'), value="=Q%s/O%s" % (index, index))

            # 是否设置了看护者
            caregivers = db_engine.Caregiver.objects(caredUserPhone=user.userPhone)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Has user set carer'), value="Y" if len(caregivers) > 0 else "N")
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Smoking status'), value="Y" if user.isSmoke == 1 else "N")
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Hypertension Status'), value="Y" if user.hasHighBp == 1 else "N")
            # 血压值(最近一次输入的高血压值)
            bps = db_dashboard.Bp.objects(userPhone=user.userPhone)
            #numbers of bps
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved BP'), value=bps.count())
            if len(bps) == 0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Systolic BP value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last Systolic BP input'), value="")
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Diastolic BP value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last Diastolic BP input'), value="")
            else:
                bp = bps[0]
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Systolic BP value (Last reported value)'), value=aesCipher.decrypt(bp.bpHighIndex))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last Systolic BP input'),
                                                 value=az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Diastolic BP value (Last reported value)'), value=aesCipher.decrypt(bp.bpLowIndex))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last Diastolic BP input'),
                                                 value=az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
            #胆固醇
            cholestrols = db_dashboard.Cholesterol.objects(userPhone=user.userPhone)
            #numbers of choles
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved LDL-C'), value=cholestrols.count())
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved HDL-C'), value=cholestrols.count())
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved TC'), value=cholestrols.count())
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved TG'), value=cholestrols.count())
            if len(cholestrols) == 0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('LDL Value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last LDL data input'), value="")
                cpUserOperationReportTable.cell(row=index, column=itemList.index('HDL Value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last HDL data input'), value="")
                cpUserOperationReportTable.cell(row=index, column=itemList.index('TC Value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last TC data input'), value="")
                cpUserOperationReportTable.cell(row=index, column=itemList.index('TG Value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last TG data input'), value="")
            else:
                cholestrol = cholestrols[0]
                cpUserOperationReportTable.cell(row=index, column=itemList.index('LDL Value (Last reported value)'), value=aesCipher.decrypt(cholestrol.low))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of last LDL data input'),
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('HDL Value (Last reported value)'), value=aesCipher.decrypt(cholestrol.high))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last HDL data input'),
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('TC Value (Last reported value)'), value=aesCipher.decrypt(cholestrol.tc))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last TC data input'),
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('TG Value (Last reported value)'), value=aesCipher.decrypt(cholestrol.tg))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last TG data input'),
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
            #BMI值(最近一次得到BMI值)
            bmis = db_dashboard.Bmi.objects(userPhone=user.userPhone)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved weight'), value=bmis.count())
            if len(bmis) == 0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('BMI value (Last reported value)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last BMI value input'), value="")
                cpUserOperationReportTable.cell(row=index, column=itemList.index('BMI target (Last reported value and date)'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last BMI target input'), value="")
            else:
                bmi = bmis[0]
                cpUserOperationReportTable.cell(row=index, column=itemList.index('BMI value (Last reported value)'), value=aesCipher.decrypt(bmi.bmiIndex))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last BMI value input'),
                                                 value=az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('BMI target (Last reported value and date)'), value=aesCipher.decrypt(bmi.targetBmiIndex))
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Date of Last BMI target input'),
                                                 value=az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))

            #本周运动分钟/个人设定运动指标（大等于1，Y；小于1，N)
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisWeekLastDayTimestamp = rest.thisWeekLastDayTimestamp(datetime.fromtimestamp(now, tz))
            exericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp,
                                                                   thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                   user.userPhone)
            thisWeekTarget = 150.0
            thisWeekMin = 0.0
            for exericeLog in exericeLogs7:
                thisWeekMin = exericeLog.totalMinute + thisWeekMin
                thisWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Has the user reach the Exercise Target Activity minutes/personal goals(min)'), value="Y" if thisWeekMin >= thisWeekTarget else "N")
            #上周设定的最后一个目标运动时间
            lastExericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                       thisWeekLastDayTimestamp - 14 * 24 * 60 * 60,
                                                                       user.userPhone)
            lastWeekTarget = 150.0
            lastWeekMin = 0.0
            for exericeLog in lastExericeLogs7:
                lastWeekMin = exericeLog.totalMinute + lastWeekMin
                lastWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Last Target Activity minutes in last calendar week(min)'), value=lastWeekTarget)
            #上个自然周运动时间
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Total Activity minutes in last calendar week(min)'), value=lastWeekMin)
            #Exercises Trends(Activity Minutes this week/Activity Minutes last week)
            if thisWeekMin == 0.0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Exercises Trends (Activity Minutes this week/Activity Minutes last week)'), value=0)
            if lastWeekMin == 0.0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Exercises Trends (Activity Minutes this week/Activity Minutes last week)'), value=1)
            else:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Exercises Trends (Activity Minutes this week/Activity Minutes last week)'), value=thisWeekMin/lastWeekMin)
            #已经完整回答的临床问卷数量
            completeSurveyCount = db_engine.SurveyResult.objects(userPhone=user.userPhone).count()
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of completed clinical survey'), value=completeSurveyCount)
            #已经推送的临床问卷数量
            surveySchedules = surveyInstanceDB.SurveySchedule.objects(userPhone=user.userPhone)
            if surveySchedules.count()>0:
                surveySchedule = surveySchedules[0]
                dayInterval = surveyInstanceDB.calDayIntervalForExport(surveySchedule.firstDone, now)
                if completeSurveyCount < 2:
                    #时间快到第一次开始RAQ时间,却只完成了两份问卷以内
                    if completeSurveyCount == 0:
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=1)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value="=E%s/F%s" % (index, index))
                    else:
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=2)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                # elif dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=1)
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value="=E%s/F%s" % (index, index))
                # elif dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V1':
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=2)
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                #                                    "=E%s/F%s" % (index, index))
                elif dayInterval == 3:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=2)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 7:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=3)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 11:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=4)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 15:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=5)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 19:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=6)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 23:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=7)
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                elif dayInterval == 24:
                    if aesCipher.decrypt(surveySchedule.currentSurvey) == 'RAQ':
                        #需要完成第六次RAQ
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=8)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=9)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V2':
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=10)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                    else:
                        #理论上不会到这里
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=8)
                        cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                                                   "=E%s/F%s" % (index, index))
                # else:
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=db_engine.SurveyResult.objects(userPhone=user.userPhone).count())
                #     cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=
                #                                    "=E%s/F%s" % (index, index))
            else:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of total pushed survey'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Acceptance on Clinical Survey'), value=0)

            #健康指数的值
            myHealthIndex = rest.calHealthIndex(user.userPhone)
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Health Index'), value=myHealthIndex)
            healthIndexCount = 0
            baseHealthIndexCount = 0.0000000001
            for u in users:
                if u.patientCode:
                    if not u.userPhone == user.userPhone:
                        if not healthDic.has_key(u.userPhone):
                            thisHealthIndex = rest.calHealthIndex(u.userPhone)
                            healthDic[u.userPhone] = thisHealthIndex
                        else:
                            thisHealthIndex = healthDic[u.userPhone]
                        if myHealthIndex > thisHealthIndex:
                            healthIndexCount += 1
                        baseHealthIndexCount += 1
            print '------------------'
            print healthIndexCount
            print baseHealthIndexCount
            print '=================='
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Ranking of Health Index (Percentage in total user)'), value='%s%%' % (healthIndexCount*1.0/baseHealthIndexCount*100.0))
            #完整回答健康问答次数
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of completed Health Knowledge Quiz'), value=topic.QuestionRecord.objects(userPhone=user.userPhone).count())
            #进入健康问答次数
            questionPopularities = api_statistics.QuestionPopularity.objects(userPhone=user.userPhone)
            if questionPopularities.count() == 0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Times of User did the heath quiz'), value=0)
            else:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Times of User did the heath quiz'), value=questionPopularities[0].totalCount)
            #访问频率
            homePopularities = api_statistics.HomePopularity.objects(userPhone=user.userPhone).order_by('-lastModifyDate')
            if homePopularities.count() == 0:
                cpUserOperationReportTable.cell(row=index, column=itemList.index('App Loyalty-1'), value=0)
                cpUserOperationReportTable.cell(row=index, column=itemList.index('App Loyalty-2'), value=0)
            else:
                homePopularity = homePopularities[0]
                #Last Date of Opening App
                cpUserOperationReportTable.cell(row=index, column=itemList.index('Last Date of Opening App'), value=az_dateutil.timestamp2datetime(homePopularity.lastModifyDate).strftime("%d %b %Y"))
                homePopularities7 = db_dashboard.queryHomeLogBetweenDays(now - 7 * 24 * 60 * 60, now, user.userPhone)
                if homePopularities7.count() == 0:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('App Loyalty-1'), value=0)
                else:
                    cpUserOperationReportTable.cell(row=index, column=itemList.index('App Loyalty-1'), value=homePopularities7.count())
                cpUserOperationReportTable.cell(row=index, column=itemList.index('App Loyalty-2'), value=homePopularities.count())


            #Number of times the user has saved exerices
            exericeLogs = db_dashboard.queryExericeLogByUserPhone(user.userPhone)

            count = 0
            for exericelog in exericeLogs:
                exerices = db_dashboard.queryExerices(str(exericelog.id))
                count += exerices.count()
            cpUserOperationReportTable.cell(row=index, column=itemList.index('Number of times the user has saved exercise'), value=count)

            index += 1

    wb.save("static/EventTrackerReport_v2.xlsx")


# LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
#
#
# def excel_style(row, col):
#     """ Convert given row and column number to an Excelstyle cell name. """
#     result = []
#     while col:
#         col, rem = divmod(col - 1, 26)
#         result[:0] = LETTERS[rem]
#     return ''.join(result) + str(row)


def calculate_age(born):
    today = datetime.today()
    try:
        birthday = born.replace(year=today.year)
    except ValueError:  # raised when birth date is February 29 and the current year is not a leap year
        birthday = born.replace(year=today.year, day=born.day - 1)
    if birthday > today:
        return today.year - born.year - 1
    else:
        return today.year - born.year


def diffDay(day):
    today = datetime.today()
    today = today.replace(hour=0, minute=0, second=0, microsecond=0)
    day = day.replace(hour=0, minute=0, second=0, microsecond=0)
    return (today - day).days


if __name__ == "__main__":
    open_excelByOpenXL(file='New_Template_for_Crestor_app_Event_Tracking.xlsx')